<?php
/*
 * SQL Generator
 * 用于sql语句构建
 * 
 * @since 
 *      1.0 2014-8-22 by sutroon
 */
require 'Inc/incDbHead.php';

if ($action == 'showconnection') {
    $r = mysql_query("SELECT DATABASE()") or die(mysql_error());
    die('DB:' . mysql_result($r, 0));
}
?>
<!DOCTYPE html>
<html>
    <head>
        <title>SoDatabaseHelper</title>
        <meta charset="UTF-8">
        <style type='text/css'>
            body { font-size:12px;}
            table{width:100%; border: solid 1px #CCC; border-left:none; border-bottom: none;}
            ul, li { margin: 0px; list-style: none; padding:0px;}
            li { line-height: 18px;}
            th:first-child{width:30px;}
            .th-field {width:500px;}
            th, td { padding: 5px; border:solid 1px #CCC; border-right: none; border-top: none;}
            tr.tr-alt { background-color: #efefef;}
            a { text-decoration: none;}
            fieldset textarea { width:100%;}
            .aside {width:180px; float:left; overflow: hidden;}
            .aside fieldset { margin: 0px 5px 5px 5px;}
            .aside ul { max-height:300px; width:160px; overflow: scroll;}
            .aside a { color:#333;}
            .aside .cur { color:#090; font-weight: bold;}
            .main {width:750px; float:left; border:solid 1px #090;}
            #result-panel {height:450px; width:720px; overflow: scroll;}
            #result-panel td { font-size:11px;}
        </style>
        <script type="text/javascript" src="/Public/Script/jquery-1.11.1.min.js"></script>
    </head>
    <body>
        <?php
        // 显示数据库登录表单
        if (!$conn_info) {
            echoDbLoginForm();
        }
        // 全局菜单
        include 'Inc/incMenu.php';

        $type = isset($_GET['type']) ? $_GET['type'] : ''; // 获取当前对象类型
        $object = isset($_GET['object']) ? $_GET['object'] : ''; // 获取当前对象名称
        $q = isset($_GET['q']) ? $_GET['q'] : ''; // 获取当作操作请求代号
        $sql = I('sql'); // 获取当前的执行脚本
        if ($sql) {
            $sql = trim($sql);
        }
        ?>        
        <div class="aside">
            <fieldset>
                <legend>Tables</legend>
                <ul>
                    <?php
                    // 显示所有数据表
                    $result = mysql_query("select TABLE_SCHEMA, TABLE_NAME, TABLE_TYPE, `ENGINE`, TABLE_ROWS, DATA_LENGTH, AUTO_INCREMENT, CREATE_TIME, `TABLE_COLLATION`, TABLE_COMMENT  from  information_schema.TABLES where TABLE_TYPE='BASE TABLE' and TABLE_SCHEMA='$database';");
                    while ($row = mysql_fetch_array($result, MYSQL_ASSOC)) {
                        echo '<li>[<a href="?type=TABLE&object=' . $row['TABLE_NAME'] . '&q=C">C</a><a href="?type=TABLE&object=' . $row['TABLE_NAME'] . '&q=U">U</a><a href="?type=TABLE&object=' . $row['TABLE_NAME'] . '&q=R">R</a><a href="?type=TABLE&object=' . $row['TABLE_NAME'] . '&q=D">D</a>]<a href="?type=TABLE&object=' . $row['TABLE_NAME'] . '"' . ($object && $row['TABLE_NAME'] == $object ? ' class="cur"' : '') . '>' . $row['TABLE_NAME'] . '</a></li>';
                    }
                    ?>
                </ul>
            </fieldset>
            <fieldset>
                <legend>Views</legend>                
                <ul>
                    <?php
                    // 显示所有视图
                    $result = mysql_query("select TABLE_SCHEMA, TABLE_NAME, VIEW_DEFINITION from information_schema.VIEWS where TABLE_SCHEMA='$database';");
                    while ($row = mysql_fetch_array($result, MYSQL_ASSOC)) {
                        echo '<li><a href="?type=VIEW&object=' . $row['TABLE_NAME'] . '"' . ($object && $row['TABLE_NAME'] == $object ? ' class="cur"' : '') . '>' . $row['TABLE_NAME'] . '</a></li>';
                    }
                    ?>
                </ul>
            </fieldset>
            <fieldset>
                <legend>Procedures</legend>
                <ul>
                    <?php
                    // 显示所有存储过程
                    $result = mysql_query("show procedure status;");
                    while ($row = mysql_fetch_array($result, MYSQL_ASSOC)) {
                        if ($row['Db'] == $database) {
                            echo '<li><a href="?type=PROCEDURE&object=' . $row['Name'] . '"' . ($object && $row['Name'] == $object ? ' class="cur"' : '') . '>' . $row['Name'] . '</a></li>';
                        }
                    }
                    ?>
                </ul>
            </fieldset>
            <fieldset>
                <legend>Functions</legend>
                <ul>
                    <?php
                    // 显示所有函数
                    $result = mysql_query("show function status;");
                    while ($row = mysql_fetch_array($result, MYSQL_ASSOC)) {
                        if ($row['Db'] == $database) {
                            echo '<li><a href="?type=FUNCTION&object=' . $row['Name'] . '"' . ($object && $row['Name'] == $object ? ' class="cur"' : '') . '>' . $row['Name'] . '</a></li>';
                        }
                    }
                    ?>
                </ul>
            </fieldset>
        </div>
        <div class="main">
            <fieldset>
                <legend>SQL-Script:</legend>
                <form method="post" action="<?php echo "?type=$type&object=$object" ?>">
                    <?php
                    echo '<textarea name="sql" rows="9" cols="90">';
                    if ($sql) {
                        echo $sql;
                    } else {
                        if ($type && $object) {
                            switch ($type) {
                                case 'TABLE':
                                    switch ($q) {
                                        case 'C':
                                            $arrout = get_insert_table_sql($object);
                                            echo $arrout[0] . PHP_EOL;
                                            echo '=========Lite============' . PHP_EOL;
                                            echo $arrout[1] . PHP_EOL;
                                            echo '=========Default=========' . PHP_EOL;
                                            echo $arrout[2] . PHP_EOL;
                                            break;
                                        case 'U':
                                            echo get_update_table_sql($object);
                                            break;
                                        case 'R':
                                            echo get_select_table_sql($object);
                                            break;
                                        case 'D':
                                            echo "delete from $object;";
                                            break;
                                        default:
                                            $result = mysql_query("show create table $object;");
                                            $row = mysql_fetch_array($result, MYSQLI_ASSOC);
                                            echo $result ? $row["Create Table"] : '';
                                            break;
                                    }
                                    break;
                                case 'VIEW':
                                    $result = mysql_query("show create view $object;");
                                    $row = mysql_fetch_array($result, MYSQLI_ASSOC);
                                    echo $result ? $row["Create View"] : '';
                                    break;
                                    ;
                                case 'PROCEDURE':
                                    $result = mysql_query("show create procedure $object;");
                                    $row = mysql_fetch_array($result, MYSQLI_ASSOC);
                                    echo $result ? $row["Create Procedure"] : '';
                                    break;
                                case 'FUNCTION':
                                    $result = mysql_query("show create function $object;");
                                    $row = mysql_fetch_array($result, MYSQLI_ASSOC);
                                    echo $result ? $row["Create Function"] : '';
                                    break;
                            }
                        }
                    }
                    echo '</textarea>';
                    ?>
                    <input type="submit" value="Execute" /><input type="reset" value="Clear" /> 
                    示例：
                    <a href="#" class="lnk-demo">更改表结构</a>
                    <a href="#" class="lnk-demo">更改视图</a>
                    <a href="#" class="lnk-demo">更改存储过程</a>
                    <a href="#" class="lnk-demo">更改函数</a>
                </form>
            </fieldset>
            <fieldset style="margin-top: 5px;">
                <legend>Result:</legend>
                <div id="result-panel">
                    <?php
                    if ($sql) {
                        $result = mysql_query($sql);
                        if (strpos($sql, ' ') === false) {
                            echo '脚本无效：' . $sql;
                        } else {
                            $action = strtolower(substr($sql, 0, strpos($sql, ' ')));
                            $result = mysql_query($sql);
                            if (strpos(',select,describe,desc,show,', ",$action,") === false) {
                                echo '受影响行数：' . $result;
                            } else {
                                echo '<table>';
                                $i = 0;
                                while ($row = mysql_fetch_array($result, MYSQL_ASSOC)) {
                                    if ($i < 1) {
                                        echo '<tr>';
                                        foreach ($row as $n => $v) {
                                            echo '<th>' . $n . '</th>';
                                        }
                                        echo '</tr>';
                                    }
                                    echo '<tr>';
                                    foreach ($row as $n => $v) {
                                        echo '<td>' . $v . '</td>';
                                    }
                                    echo '</tr>';
                                    $i++;
                                }
                                echo '</table>';
                            }
                        }
                    }
                    // 释放资源
                    if ($result) {
                        //mysql_free_result($result);
                    }
                    unset($db);
                    ?>
                </div>
            </fieldset>
        </div>
        <div style="clear: both;">&nbsp;</div>        
    </body>
    <script type="text/javascript">
        // fieldset标题点击收起或展开 2014-8-22 by sutroon
        $('legend').attr('title', '点击展开或收起').click(function() {
            $(this).next().toggle();
        })

        // 实例语句 2014-8-27 by sutroon
        $('.lnk-demo').click(function() {
            var str = '';
            switch ($(this).text()) {
                case '更改表结构':
                    str = '====修改列(名称、类型、注释)====\n';
                    str += "alter table `database_name`.`table_name` change column `old-column-name` `new-column-name` INT(11) NOT NULL DEFAULT '0' COMMENT '注释内容';\n";
                    str += '\n====表重命名(名称、引擎、注释)====\n';
                    str += 'ALTER TABLE `old_database_name`.`old_table_name` RENAME TO  `new_database_name`.`new_table_name`;\n';
                    str += "ALTER TABLE `old_database_name`.`old_table_name` RENAME TO  `new_database_name`.`new_table_name`, COLLATE = utf8_bin, ENGINE = MyISAM, COMMENT = '数据表 1.0 2014-7-12 by sutroon';\n";
                    break;
                case '更改视图':
                    str = 'drop view if exists `view_name`;\n';
                    str += 'create view `view_name` as select a,b,c from mytable;';
                    break;
                case '更改存储过程':
                    str = 'drop procedure if exists `proc_name`;\n';
                    str += 'DELIMITER $$\n';
                    str += 'create procedure `proc_name`(pint_id int, pstr_name varchar(32), pdat_time datetime)\n';
                    str += 'begin\n';
                    str += '\tdeclare vstr_name varchar(32);\n';
                    str += '\tdeclare vint_id int default 0;\n';
                    str += 'end;$$\n';
                    str += 'DELIMITER ;\n';
                    break;
                case '更改函数':
                    str = 'drop function if exists `func_name`;\n';
                    str += 'DELIMITER $$\n';
                    str += 'create function `func_name`(pint_id int, pstr_name varchar(32), pdat_time datetime)\n';
                    str += 'begin\n';
                    str += '\tdeclare vstr_name varchar(32);\n';
                    str += '\tdeclare vint_id int default 0;\n';
                    str += 'end;$$\n';
                    str += 'DELIMITER ;\n';
                    break;
                    break;
            }
            $('textarea[name="sql"]').val(str);
            return false;
        })
    </script>
</html>